create database LoanPS
go
use LoanPS
go
create table TaiKhoan(
MaTaiKhoan int  not null,
TenTaiKhoan nvarchar(50) not null,
MaNV nvarchar(50) not null,
MaCap int not null,
primary key(MaTaiKhoan)
);
drop table TaiKhoan;
create table NhanVien(
MaNV nvarchar(50) primary key not null,
HoTenNV nvarchar(50) not null,
GioTinh nvarchar(5) not null,
DiaChi nvarchar(50) not null,
NgaySinh datetime not null,
MaPhongBan nvarchar(50) constraint FK_PhongBan foreign key references PhongBan(MaPhongBan) not null,
MaChucVu nvarchar(50) constraint FK_ChucVu foreign key references ChucVu(MaChucVu) not null
);
create table PhongBan(
MaPhongBan nvarchar(50) primary key not null,
TenPhongBan nvarchar(50) not null

);
create table ChucVu(
MaChucVu nvarchar(50) primary key not null,
TenChucVu nvarchar(50) not null
);
create table KheUoc(
MaKheUoc int primary key not null,
TenKhachHang nvarchar(50) not null,
MaDoiTac nvarchar(50)          constraint FK_MaDoitac       foreign key references DoiTac(MaDoiTac) not null,
MaNV nvarchar(50)              constraint FK_MaNV           foreign key references NhanVien(MaNV) not null,
MaHinhThucDamBao nvarchar(50)  constraint FK_HinhThucDamBao foreign key references HinhThucDamBao(MaHinhThucDamBao) not null,
MaMucDicVay nvarchar(50)       constraint FK_MucDichVay     foreign key references MucDichVay(MaMucDichVay) not null,
MaKieuTra nvarchar(50)         constraint FK_kieutra        foreign key references KieuTra(Makieutra) not null,
MaLaiXuat Nvarchar(50)         constraint FK_LaiXuat        foreign key references LaiXuat(MaLaiXuat) not null,
NgayVay datetime not null,
NgayTra datetime not null,
SoTienGoc int not null,
SoTienLai int not null,
SoTienPhaiTra int not null,
SoTienConNo int not null,
DaThanhToanXong nvarchar(5) not null
);
create table DoiTac(
MaDoiTac nvarchar(50) not null,
HoTenDoiTac nvarchar(50) not null,
GioiTinh nvarchar(5) not null,
DiaChi nvarchar(50) not null,
NgaySinh datetime not null,
CMTND int not null,
NgayCap datetime not null,
NoiCap nvarchar(50) not null,
MaTinhMaHuyenMaXa nVarchar(50) not null,
DanToc nvarchar(50) not null,
MaLoaiHinh nvarchar(50) constraint FK_LoaiHinh foreign key references LoaiHinh(MaLoaiHinh) not null,
primary key(MaDoiTac)
);
drop table doitac;
select * from DoiTac;
create table LoaiHinh(
MaLoaiHinh nvarchar(50) primary key not null,
LoaiHinhVay nvarchar(50) not null
);
create table HinhthucDamBao(
MaHinhThucDamBao nvarchar(50) primary key not null,
TenHinhThucDamBao nvarchar(50) not null

);
 create table MucDichVay(
MaMucDichVay nvarchar(50) primary key not null,
TenMucDicVay nvarchar(50) not null
);
create table KieuTra(
MaKieuTra nvarchar(50) primary key not null,
TenKieuTra nvarchar(50) not null
);
create table LaiXuat(
MaLaiXuat nvarchar(50) primary key not null,
LaiXuat int not null
);
create table GiaiNgan(
MaGiaiNgan nvarchar(50) primary key not null,
NgayGiaiNgan datetime not null,
MaKheUoc int constraint FK_KheUoc2 foreign key references KheUoc(MaKheUoc) not null,
MaTaiKhoan int constraint FK_MaTaiKhoan foreign key references TaiKhoan(MaTaiKhoan) not null,
SoTien int not null
);
create table HoaHong (
MaHoaHong nvarchar(50) primary key not null,
MaKheUoc int constraint FK_KheUoc1 foreign key references KheUoc(MaKheUoc) not null,
NgayNhan datetime not null,
SoTien int not null
);
create table PhanQuyen(
MaNV nvarchar(50) not null,
UserName nvarchar(50) not null,
[PassWord] nVarchar(50) not null,
MaQuyen nvarchar(50) constraint FK_MaQuyen foreign key references QuyenDangNhap(MaQuyen) not null,
primary key(MaNV,UserName,MaQuyen)

);
create table QuyenDangNhap(
MaQuyen nvarchar(50) primary key not null,
TenQuyen nvarchar(50) not null
);
--insert gia tri bang Tai Khoan
insert into TaiKhoan values('0000001','Tai Khoan 1a1','NV0001','0001');
insert into TaiKhoan values('0000002','Tai Khoan 1a2','NV0001','0002');
insert into TaiKhoan values('0000003','Tai Khoan 2b1','NV0002','0003');
insert into TaiKhoan values('0000004','Tai Khoan 2b2','NV0002','0004');
insert into TaiKhoan values('0000005','Tai Khoan 3c1','NV0003','0005');
insert into TaiKhoan values('0000006','Tai Khoan 3c2','NV0003','0006');
insert into TaiKhoan values('0000007','Tai Khoan 3c3','NV0003','0007');
-- insert gia tri vao bang Phong Ban
insert into PhongBan values('PB001a','phong ban 1a');
insert into PhongBan values('PB001b','phong ban 1b');
insert into PhongBan values('PB001c','phong ban 1c');
insert into PhongBan values('PB001d','phong ban 1d');
insert into PhongBan values('PB002a','phong ban 2a');
insert into PhongBan values('PB003a','phong ban 3a');
insert into PhongBan values('PB004a','phong ban 4a');
insert into PhongBan values('PB004b','phong ban 4b');
-- insert gia tri vao bang chuc vu 
insert into ChucVu values('CV001a','truong phong 1a');
insert into ChucVu values('CV0011a','Nhan vien phong 1a');
insert into ChucVu values('CV001b','truong phong 1b');
insert into ChucVu values('CV0011b','Nhan vien phong 1b');
insert into ChucVu values('CV001c','truong phong 1c');
insert into ChucVu values('CV0011c','Nhan Vien phong 1a');
insert into ChucVu values('CV001d','truong phong 1d');
insert into ChucVu values('CV0011d','Nhan Vien phong 1d');
insert into ChucVu values('CV002a','truong phong 2a');
insert into ChucVu values('CV0021a','nhan vien phong 2a');
insert into ChucVu values('CV003a','truong phong 3a');
insert into ChucVu values('CV0031a','Nhan Vien phong 3a');
insert into ChucVu values('CV004a','truong phong 4a');
insert into ChucVu values('CV0041a','Nhan Vien phong 3a');
insert into ChucVu values('CV001','Quan ly nhan 1');
insert into ChucVu values('CV002','Quan ly nhan su 2');
insert into ChucVu values('CV003','Quan ly nhan su 3');
insert into ChucVu values('CV004','Quan ly nhan su 4');
--insert bang nhan vien
insert into NhanVien values('NV0001','Nguyen Van Nghia','Nam','Bac gian','07/02/1991','PB001a','CV001');
insert into NhanVien values('NV0002','Do Quang Sang','Nam','Thai Binh','10/04/1991','PB001a','CV001a');
insert into NhanVien values('NV0003','Nguyen Van Tuan','Nam','Ha Noi','3/28/1991','PB001a','CV0011a');
insert into NhanVien values('NV0004','Ha Van Son','Nam','Tuyen Quang','3/8/1991','PB002a','CV002');
insert into NhanVien values('NV0005','Pham Hoang Hai','Nu','Thanh Hoa','08/19/1991','PB002a','CV002a');
insert into NhanVien values('NV0006','Le Dinh Phu Hoang','Nam','Ha Noi','10/20/1991','PB002a','CV0021a');
insert into NhanVien values('NV0007','Pham Quang Hung','Nam','Ha Noi','11/20/1991','PB003a','CV003');
insert into NhanVien values('NV0008','Nguyen Vu Quyen','Nam','Ha Noi','9/02/1991','PB003a','CV003a');
insert into NhanVien values('NV0009','Nguyen Manh Quan','Nam','Bac giang','3/10/1991','PB003a','CV0031a');
insert into NhanVien values('NV00010','Vu Thi Thu Huong','Nu','Mien Trung','5/1/1991','PB001b','CV001b');
insert into NhanVien values('NV00011','Nguyen Thi Loan','Nu','Bac Giang','4/30/1991','PB001b','CV0011b');
insert into NhanVien values('NV00012','Luu Diec Phi','Nu','Trung Quoc','1/1/1991','PB001c','CV001c');
insert into NhanVien values('NV00013','Jame Born','Nam','Hollywood','04/04/1991','PB001c','CV0011c');
--insert gia tri vao bang loai hinh
insert into LoaiHinh values('LH01','Vay cho ca nhan');
insert into LoaiHinh values('LH02','Vay cho to chuc');
insert into LoaiHinh values('LH03','Vay Tap The');

--insert gia tri Bang Doi Tac
insert into DoiTac values('DT0001','Nguyen Van a','Nam','Lao Cai','01/01/1981','1000000001','9/13/2002','11-12-13','Lao Cai','San riu','LH01');
insert into DoiTac values('DT0002','Nguyen Van b','Nam','Ha Gian','01/02/1981','1100000001','9/14/2002','01-12-13','Ha Giang','Kinh','LH01');
insert into DoiTac values('DT0003','Nguyen Van c','Nam','Tuyen Quang','01/03/1981','1200000001','9/15/2002','02-12-13','Tuyen Quang','Thai','LH02');
insert into DoiTac values('DT0004','Nguyen Van d','Nam','Lang Son','01/04/1981','1300000001','9/16/2002','03-12-13','Lang Son','Kinh','LH02');
insert into DoiTac values('DT0005','Nguyen Van e','Nam','Cao Bang','01/05/1981','1400000001','9/17/2002','04-12-13','Cao Bang','H.Mong','LH03');
-- insert gia tri bang Hinh thuc dam bao
insert into HinhthucDamBao values('DB001','The chap bat dong san nha dat');
insert into HinhthucDamBao values('DB002','The chap tai san ca nhan co gia tri');
insert into HinhthucDamBao values('DB003','The chap vang');
insert into HinhthucDamBao values('DB004','The chap Cong ty');
-- insert bang muc dich vay
insert into MucDichVay values('MD001','Vay Cho Giao Duc');
insert into MucDichVay values('MD002','Vay Cho ca nhan');
insert into MucDichVay values('MD003','Vay Cho Mua Nha');
insert into MucDichVay values('MD004','Vay Cho Mua Xe');
-- insert gia tri vao bang kieu tra
insert into KieuTra values('KT001','Tra lai theo thang');
insert into KieuTra values('KT002','Tra lai theo Quy');
insert into KieuTra values('KT003','Tra lai theo Nam');
insert into KieuTra values('KT004','Tra gop trong 10 nam');
-- insert bang ma lai xuat
insert into LaiXuat values('LX004','13');
insert into LaiXuat values('LX003','10');
insert into LaiXuat values('LX002','18');
insert into LaiXuat values('LX001','5');
--insert Bang Phan Quyen
insert into QuyenDangNhap values('CV001','Admin');
insert into QuyenDangNhap values('CV002','Admin');
insert into QuyenDangNhap values('CV003','Admin');
insert into QuyenDangNhap values('CV004','Admin');
insert into QuyenDangNhap values('CV001a','nhan vien');
insert into QuyenDangNhap values('CV001b','Nhan vien');
insert into QuyenDangNhap values('CV001c','Nhan vien');
insert into QuyenDangNhap values('CV001d','Nhan vien');
insert into QuyenDangNhap values('CV002a','Nhan vien');
insert into QuyenDangNhap values('CV0021a','Nhan vien');
insert into QuyenDangNhap values('CV003a','Nhan vien');
insert into QuyenDangNhap values('CV0031a','Nhan vien');
insert into QuyenDangNhap values('CV004a','Nhan vien');
insert into QuyenDangNhap values('CV0041a','Nhan vien');
insert into QuyenDangNhap values('CV0011a','Nhan vien');
insert into QuyenDangNhap values('CV0011b','Nhan vien');
insert into QuyenDangNhap values('CV0011c','Nhan vien');
insert into QuyenDangNhap values('CV001c','Nhan vien');
insert into QuyenDangNhap values('CV001c','Nhan vien');
insert into QuyenDangNhap values('CV001c','Nhan vien');
-- insert 
